package org.openapi.service.impl;

import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONArray;
import lombok.extern.slf4j.Slf4j;
import org.openapi.common.ApiException;
import org.openapi.common.ApiUser;
import org.openapi.consts.ApiConst;
import org.openapi.consts.EventType;
import org.openapi.consts.LogType;
import org.openapi.event.EventBus;
import org.openapi.service.IApiLoaderService;
import org.openapi.service.IDbService;
import org.openapi.service.IOpenApiService;
import org.openapi.utils.JsUtil;
import org.openapi.utils.SqlUtil;
import org.openapi.vo.RelQuery;
import org.openapi.vo.SqlPager;
import org.openapi.vo.TableData;
import org.openapi.vo.TableQuery;
import org.openapi.domain.ApiEvent;
import org.openapi.domain.ApiModel;
import org.openapi.utils.StrUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Service;

import java.util.*;
import java.util.stream.Collectors;

@Service
@Slf4j
public class DbServiceImpl implements IDbService {
    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;
    @Autowired
    private IApiLoaderService apiLoaderService;
    @Autowired
    private IOpenApiService openApiService;

    /**
     * 查询一条SQL
     * @param sql
     * @return
     */
    @Override
    public List<Map<String,Object>> query(String sql){
        log.debug("sql: {}", sql.replaceAll("\\n", " "));
        return jdbcTemplate.queryForList(sql, new HashMap<>());
    }

    /**
     * 更新
     * @param sql
     * @return
     */
    @Override
    public int exec(String sql){
        log.debug("sql: {}", sql.replaceAll("\\n", " "));
        return jdbcTemplate.update(sql, new HashMap<>());
    }

    /**
     * 联表分页查询
     * @param query
     * @return
     */
    @Override
    public List<Map<String,Object>> query(RelQuery query){
        String sql = querySql(query);
        Map<String,Object>  params = query.getParams();
        autoData(params);
        if(query.getPager() != null) {
            params.put("offset", query.getPager().getOffset());
            params.put("limit", query.getPager().getLimit());
            sql = SqlUtil.pageSql(sql, query.getPager());
            log.debug("sql: {}\r\nparams: {}", sql.replaceAll("\\n", " "), params);
            return jdbcTemplate.queryForList(sql, params);
        }else{
            log.debug("sql: {}\r\nparams: {}", sql.replaceAll("\\n", " "), params);
            return jdbcTemplate.queryForList(sql, params);
        }
    }

    /**
     * 联表单条查询
     * @param query
     * @return
     */
    @Override
    public Map find(RelQuery query){
        Map<String,Object>  params = query.getParams();
        autoData(params);
        String sql = querySql(query) + " LIMIT 1";
        log.debug("sql: {}\r\nparams: {}", sql.replaceAll("\\n", " "), params);
        return jdbcTemplate.queryForMap(sql, params);
    }

    /**
     * 单表分页查询
     * @param query
     * @return
     */
    @Override
    public List<Map<String,Object>> query(TableQuery query){
        String sql = querySql(query);
        Map<String,Object>  params = query.getParams();
        autoData(params);
        //加入分页
        params.put("offset", query.getPager().getOffset());
        params.put("limit", query.getPager().getLimit());
        sql = SqlUtil.pageSql(sql, query.getPager());
        log.debug("sql: {}\r\nparams: {}", sql.replaceAll("\\n", " "), params);
        return jdbcTemplate.queryForList(sql, params);
    }

    /**
     * 分页查询
     * @param query
     * @return
     */
    @Override
    public Map page(TableQuery query){
        return  page(querySql(query), query.getParams(), query.getPager());
    }

    /**
     * 分页查询
     * @return
     */
    @Override
    public Map page(String sql, Map<String,Object>  params,  SqlPager pager){
        autoData(params);
        Map retData = new HashMap();
        retData.put("limit", pager.getLimit());
        retData.put("page", pager.getPage());

        //查询总数
        String countSql = "SELECT COUNT(1) total FROM (" + sql + ") tmp";
        log.debug("count: {}\r\nparams: {}", sql.replaceAll("\\n", " "), params);
        Map countData = jdbcTemplate.queryForMap(countSql, params);
        int total = Integer.parseInt(countData.get("total").toString());
        retData.put("total", total);
        if(total  == 0){
            retData.put("rows", null);
            return retData;
        }

        //加入分页
        params.put("offset", pager.getOffset());
        params.put("limit", pager.getLimit());
        sql = SqlUtil.pageSql(sql, pager);
        log.debug("sql: {}\r\nparams: {}", sql.replaceAll("\\n", " "), params);
        retData.put("rows", jdbcTemplate.queryForList(sql, params));
        return retData;
    }

    /**
     * 单表单条查询
     * @param query
     * @return
     */
    @Override
    public Map find(TableQuery query){
        String sql = querySql(query) + " LIMIT 1";
        Map<String,Object>  params = query.getParams();
        try {
            autoData(params);
            log.debug("sql: {}\r\nparams: {}", sql.replaceAll("\\n", " "), params);
            return jdbcTemplate.queryForMap(sql, params);
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    /**
     * 根据指定字段查询一条数据
     * @param table
     * @param fields
     * @param data
     * @return
     */
    private Map findByField(String table, String[] fields, Map data){
        String sql = SqlUtil.findSql(table, fields, data);
        try{
            log.debug("sql: {}\r\nparams: {}", sql.replaceAll("\\n", " "), data);
            return jdbcTemplate.queryForMap(sql, data);
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    /**
     * 带数据权限的查询
     * @param query
     * @return
     */
    private String querySql(TableQuery query){
        //超级管理员不做权限验证
        if(openApiService.isAdmin()){
            return query.getSql(null);
        }

        ApiModel model = getTableModel(query);

        //加入数据权限
        ApiUser user = openApiService.getLoginUser();
        //用户权限
        if(user.getDataScope() == ApiUser.DATA_SCOPE_USER || StrUtil.isNotEmpty(model.getUserScope())){
            String scope = model.getUserScope().replaceAll("\\$\\{userId\\}", user.getUserId());
            return query.getSql(scope);
        }else if(user.getDataScope() == ApiUser.DATA_SCOPE_ORG || StrUtil.isNotEmpty(model.getOrgScope())){
            //组织权限
            String scope = model.getUserScope().replaceAll("\\$\\{orgIds\\}", "'"+StrUtil.join(user.getOrgIds(), "','")+"'");
            scope = scope.replaceAll("\\$\\{orgId\\}", user.getOrgId());
            scope = model.getUserScope().replaceAll("\\$\\{userId\\}", user.getUserId());
            return query.getSql(scope);
        }else {
            //全部权限
            return query.getSql(null);
        }
    }


    /**
     * 填充关联数据
     * @sys_user.id 关联sys_user对象的ID值,目前只支持二级结构
     * 如："userId":"@USER.id"
     * @param retMap
     * @param dataMap
     */
    @Override
    public void relData(Map<String, Map> retMap,  Map<String, Object> dataMap){
        if(retMap == null || retMap.isEmpty()){
            return;
        }
        for(String p:dataMap.keySet()){
            if(dataMap.get(p) == null){
                continue;
            }

            String v = dataMap.get(p).toString();
            if(!v.startsWith("@")){
                continue;
            }
            //自动填充参数
            String[] ps = v.split("\\.");
            if(ps.length < 2){
                continue;
            }
            //去除@符号
            String pname = ps[0].substring(1);
            if (retMap == null || !retMap.containsKey(pname)) {
                log.error("不支持的数据填充规则 {}", v);
                throw new ApiException("保存数据的业务逻辑异常");
            }
            dataMap.put(p, retMap.get(pname).get(ps[1]));
        }
    }

    /**
     * 自动填充值：
     * @userid() 当前用户ID
     * @username() 当前用户昵称
     * @orgid() 当前用户组织ID
     * @orgname() 当前用户组织名称
     * @now() 当前时间
     * @param dataMap
     */
    @Override
    public void autoData(Map<String, Object> dataMap){
        for(String field:dataMap.keySet()){
            if(dataMap.get(field) == null){
                continue;
            }

            //自动填充当前用户ID，昵称，组织ID，组织名称，当前时间
            String v = dataMap.get(field).toString();
            if (!v.startsWith("@")) {
                continue;
            }

            if (v.equalsIgnoreCase("@userid()")) {
                dataMap.put(field, openApiService.getLoginUser().getUserId());
            } else if (v.equalsIgnoreCase("@username()")) {
                dataMap.put(field, openApiService.getLoginUser().getUsername());
            } else if (v.equalsIgnoreCase("@orgid()")) {
                dataMap.put(field, openApiService.getLoginUser().getOrgId());
            } else if (v.equalsIgnoreCase("@orgname()")) {
                dataMap.put(field, openApiService.getLoginUser().getOrgName());
            } else if (v.equalsIgnoreCase("@now()")) {
                dataMap.put(field, new Date());
            }
        }
    }

    /**
     * 插入数据
     * 根据主键和唯一字段进行重复性判断，如果override=true会自动进行更新
     * @param table TableData
     * @param override 存在是否覆盖
     * @return
     */
    @Override
    public int insertData(TableData table, boolean override){
        ApiModel model = getTableModel(table);

        Map<String, Object> dataMap  = table.getData();
        //用主键判断数据是否存在
        if(dataMap.containsKey(model.getPkField()) && dataMap.get(model.getPkField()) != null){
            Map old = findByField(table.getTable(), model.getPkField().split(","), dataMap);
            if(old != null && !old.isEmpty()){
                if(override) {
                    //允许覆盖则更新
                    return updateData(table);
                }else{
                    throw new ApiException("数据已存在");
                }
            }
        }

        //唯一性验证
        if(model.getUniqueField() != null && model.getUniqueField().length >0) {
            Map oldData = findByField(table.getTable(), model.getUniqueField(), dataMap);
            if(oldData != null && !oldData.isEmpty()){
                if(override) {
                    //允许覆盖则更新
                    table.addData(model.getPkField(), oldData.get(model.getPkField()));
                    return updateData(table);
                }else {
                    log.error("table {} unique check failed:", table.getTable(), dataMap);
                    throw new ApiException(model.getName() + " 存在重复数据");
                }
            }
        }
        dataMap = SqlUtil.getInsertData(model, table);
        autoData(dataMap);

        int rows = 0;
        // 前置操作
        if(!table.getIgnore()){
            rows += runEvent(model.getId(), EventType.BI.getCode(), dataMap);
        }
        EventBus.call(model.getCode(), EventType.BI, dataMap);

        //新增数据
        GeneratedKeyHolder holder = new GeneratedKeyHolder();
        String sql = SqlUtil.insertSql(table.getTable(), dataMap);
        rows += jdbcTemplate.update(sql, new MapSqlParameterSource(dataMap), holder);
        log.debug("sql :{}\r\nparams {}", sql, dataMap);
        table.addData(model.getPkField(), holder.getKey());
        dataMap.put(model.getPkField(), holder.getKey());

        //记录日志
        log(model.getId(), LogType.INSERT.getCode(), table.getData(model.getPkField()).toString(), dataMap);

        // 后置操作
        if(!table.getIgnore()) {
            rows += runEvent(model.getId(), EventType.AI.getCode(), dataMap);
        }
        EventBus.call(model.getCode(), EventType.AI, dataMap);
        return rows;
    }

    /**
     * 更新数据
     * @param table
     * @return
     */
    @Override
    public int updateData(TableData table){
        ApiModel model = getTableModel(table);

        Object id = table.getData(model.getPkField());
        if(StrUtil.isNull(id)){
            throw new ApiException("更新数据时主键不能为空:" + model.getName());
        }

        Map<String, Object> dataMap = SqlUtil.getUpdateData(model, table);
        autoData(dataMap);

        // 前置操作
        if(!table.getIgnore()) {
            runEvent(model.getId(), EventType.BU.getCode(), dataMap);
        }
        EventBus.call(model.getCode(), EventType.BU, dataMap);

        //更新数据
        String sql = SqlUtil.updateSql(table.getTable(), model.getPkField(), dataMap);

        log.debug("sql :{}\r\nparams {}", sql, dataMap);
        int rows = jdbcTemplate.update(sql, dataMap);
        log.debug("affect {} rows", rows);

        //记录日志
        log(model.getId(), LogType.UPDATE.getCode(), table.getData(model.getPkField()).toString(), dataMap);

        // 后置操作
        if(!table.getIgnore()) {
            runEvent(model.getId(), EventType.AU.getCode(), dataMap);
        }
        EventBus.call(model.getCode(), EventType.AU, dataMap);
        return rows;
    }



    /**
     * 删除数据
     * @param table
     * @return
     */
    @Override
    public int deleteData(TableQuery table){
        ApiModel model = getTableModel(table);
        table.setColumn("*");

        //查出原始数据进行事件处理
        List<Map<String,Object>> dataList = query(table);
        if(dataList == null || dataList.size()  == 0){
            return 0;
        }

        String ids = dataList.stream().map(d->d.get(model.getPkField()).toString()).collect(Collectors.joining(","));

        //删除前验证
        if(runEvent(model.getId(), EventType.DC.getCode(), ids) > 0){
            throw new ApiException("业务关联数据不为空，当前数据不能删除");
        }

        //删除业务的前置操作
        if(!table.getIgnore()) {
            runEvent(model.getId(), EventType.BD.getCode(), ids);
        }
        EventBus.call(model.getCode(), EventType.BD, ids);

        String sql = " WHERE " + model.getPkField() + " IN ( " + ids + " )";
        if(StrUtil.isEmpty(model.getDelField())){
            //物理删除
            sql = "DELETE FROM " + table.getName(true) + sql;
        }else{
            //软删除
            sql = "UPDATE " + table.getName(true) + " SET " + model.getDelField() + " = '1' " + sql;
        }
        log.debug("delete sql :{}", sql);
        int rows = jdbcTemplate.update(sql, table.getParams());
        log.debug("affect {} rows", rows);

        //记录日志
        log(model.getId(), LogType.DELETE.getCode(), null, ids);

        //删除业务的后置操作
        if(!table.getIgnore()) {
            runEvent(model.getId(), EventType.AD.getCode(), ids);
        }
        EventBus.call(model.getCode(), EventType.AD, ids);

        return rows;
    }

    /**
     * 触发模型事件
     * @param modelId
     * @param eventType
     * @param data
     * @return
     */
    private int runEvent(Long modelId, String eventType, Object data){
        ApiEvent event = apiLoaderService.getEvent(modelId, eventType);
        if(event == null || !ApiConst.STATUS_ENABLED.equalsIgnoreCase(event.getStatus())) {
            return 0;
        }
        String sqlStr = JsUtil.exec(event.getContent(), data);
        if(StrUtil.isEmpty(sqlStr)){
            return 0;
        }
        Map np = new HashMap();
        //删除前确认
        if(eventType.equalsIgnoreCase(EventType.DC.getCode())){
            Map dcData = jdbcTemplate.queryForMap(sqlStr, np);
            log.debug("delete {} check event: {}\r\ngot:{}", modelId, sqlStr, dcData);
            if(dcData == null || dcData.isEmpty()){
                return 0;
            }else if(Integer.parseInt(dcData.values().iterator().next().toString()) == 0){
                return 0;
            }else{
                throw new ApiException(StrUtil.isEmpty(event.getRemark()) ? "关联数据不为空，不能删除" : event.getRemark());
            }
        }

        //业务事件
        int rows = 0;
        int total = 0;
        sqlStr = sqlStr.trim();
        if(sqlStr.startsWith("[")){
            JSONArray sqlArr = JSONArray.parseArray(sqlStr);
            for(int i=0;i<sqlArr.size();i++){
                log.debug("exec {} event {} sql: {}\r\n", modelId, eventType,  sqlArr.getString(i).replaceAll("\\n", " "));
                rows = jdbcTemplate.update(sqlArr.getString(i), np);
                log.debug("affect {} rows", rows);
                total += rows;
            }
        }else{
            String[] sqls = sqlStr.split(";");
            for(String sql:sqls){
                if(StrUtil.isEmpty(sql)){
                    continue;
                }
                log.debug("exec {} event {} sql: {}\r\n", modelId, eventType,  sql.replaceAll("\\n", " "));
                rows = jdbcTemplate.update(sql, np);
                log.debug("affect {} rows", rows);
                total += rows;
            }
        }

        //记录日志
        log(event.getModelId(), LogType.EVENT.getCode(), event.getId().toString(), data);
        return total;
    }

    /**
     * 记录日志
     * @return
     */
    @Override
    public void log(Long modelId, String type, String key, Object data){
        Map<String, Object> dataMap = new HashMap<>();
        dataMap.put("model_id", modelId);
        dataMap.put("log_type", type);
        dataMap.put("log_key", key);
        if(data != null){
            String dataStr;
            if(data instanceof String){
                dataStr = data.toString();
            }else{
                dataStr = JSON.toJSONString(data);
            }
            dataMap.put("log_data", dataStr.length()<500 ? dataStr : dataStr.substring(0, 499));
        }
        dataMap.put("log_time", new Date());
        ApiUser user = openApiService.getLoginUser();
        dataMap.put("user_id", user.getUserId());
        dataMap.put("user_name", user.getUsername());
        jdbcTemplate.update(SqlUtil.insertSql("api_log", dataMap), dataMap);
    }

    /**
     * 获取数据模型
     * @param tableData
     * @return
     */
    private ApiModel getTableModel(TableData tableData){
        if(tableData.getModel() == null){
            tableData.setModel(apiLoaderService.getModel(tableData.getTable()));
        }
        return tableData.getModel();
    }
}
